# import pymysql
#
# PYMYSQL_CONF = {
#     # 'host': '127.0.0.1',
#     'host': '172.16.159.132',
#     # 'password': '123456',
#     'password': 'Sqdmm123#',
#     'port': 3306,  # MySQL服务端口
#     'user': 'root',
#     'database': 'zentao',
#     'charset': 'utf8mb4'
# }
#
# # pymysql连接数据
# connection = pymysql.connect(**PYMYSQL_CONF)
# sql = '''
# SELECT
#     zt_team.root,
#     zt_user.account,
#     zt_user.realname
# FROM
#     zt_team
# JOIN
#     zt_user ON zt_team.account = zt_user.account
# WHERE
#     zt_team.root = 1740
# '''
# with connection.cursor() as cursor:
#     cursor.execute(sql)
#     data = cursor.fetchall()
#
# res = {}
# for i in data:
#     res[i[2]] = i[1]
# res['付强'] = 'fuqiang'
# res['陈红  '] = 'chenhong'
#
#
# sql = '''
# select id,finishedBy from zt_task where project=1740
# '''
# cursor = connection.cursor()
# cursor.execute(sql)
# data = cursor.fetchall()
#
#
# for i in data:
#     if i[1] in res.keys():
#         sql = f'update zt_task set finishedBy = "{res[i[1]]}" where id={i[0]}'
#         cursor.execute(sql)
#
# sql = '''
# select id,assignedTo from zt_task where project=1740
# '''
# cursor = connection.cursor()
# cursor.execute(sql)
# data = cursor.fetchall()
#
#
# for i in data:
#     if i[1] in res.keys():
#         sql = f'update zt_task set assignedTo = "{res[i[1]]}" where id={i[0]}'
#         cursor.execute(sql)
#
# connection.commit()
# cursor.close()
# connection.close()

# bug数据导入禅道
# import pandas as pd
# from zentao.settings import PYMYSQL_CONF
# import pymysql
# import re
# from datetime import datetime
#
# df = pd.read_excel('/media/977GB/wcj_work/禅道/新视图开发/营销服务事业部_数字服务平台（服务运营）V1.0-bug导入禅道数据-20241219-修正.xlsx',
#                    dtype={'创建日期': 'str',
#                             '指派日期': 'str',
#                             '解决日期': 'str',
#                             '关闭日期': 'str',
#                             '修改日期': 'str'})
#
# df = df.fillna('')
#
# #获取#后的内容
# def get_jinghao(x):
#     # print(x)
#     x = x.split('#')[-1]
#     if x[-1] in ['(','（',')','）']:
#         x = x[:-1]
#     return x
#
# # 假设get_jinghao是一个已经定义好的函数
# for column in ['所属产品', '所属模块', '所属项目', '所属执行', '指派给']:
#     df[column] = df[column].map(get_jinghao)
#
# security_map = {
#     "致命":1,
#     "严重":2,
#     "一般":3,
#     "建议":4
# }
#
# pri_map = {
#     "紧急":1,
#     "一般":2,
#     "低":3
# }
#
# bug_type_map = {
#     '其他':""
# }
#
# flawsClassList_map = {
#     "功能缺少":"funcMiss",
#     "页面未做格式约束/合法性检查":"pgFormatLegChk",
#     "数据缺失/无法验证":"dataMissVerify",
#     "功能逻辑错误":"funcLogicErr",
#     "功能与需求偏差":"funcReqDev",
#     "页面报错":"pageError",
#     "页面显示异常/提示错误":"pageDispErr",
# }
#
# testStageList = {
#     "研发阶段":"rad",
#     "测试阶段":"test",
#     "验收阶段":"acceptance",
# }
#
# bugEnvList = {
#     "研发环境":"rad",
#     "测试环境":"test",
#     "生产环境":"production",
# }
#
# bugClassList = {
#     "功能缺陷":"function",
#     "性能":"performance",
#     "安全":"safe",
#     "其他":"other"
# }
#
# status_map = {
#     '激活':'active',
#     '已解决':'resolved',
#     '已关闭':'closed'
# }
#
#
# df['严重程度'] = df['严重程度'].map(security_map)
# df['优先级'] = df['优先级'].map(pri_map)
# df['Bug类型'] = df['Bug类型'].map({'其他':'others'})
# df['缺陷/问题现象分类'] = df['缺陷/问题现象分类'].map(flawsClassList_map)
# df['测试阶段'] = df['测试阶段'].map(testStageList)
# df['Bug环境'] = df['Bug环境'].map(bugEnvList)
# df['Bug分类'] = df['Bug分类'].map(bugClassList)
# df['Bug状态'] = df['Bug状态'].map(status_map)
# df['影响版本'] = df['影响版本'].map({"主干":"trunk"})
#
# for i in ['严重程度','优先级','Bug类型','缺陷/问题现象分类','测试阶段','Bug环境','Bug分类','Bug状态','影响版本']:
#     print(i,df[i].isnull().any())
#
# users = set(list(df['由谁创建'])+list(df['指派给'])+list(df['由谁关闭']))
#
# connection = pymysql.connect(**PYMYSQL_CONF)
#
# users_string = ""
# for user in users:
#     users_string+=f"'{user}',"
#
# sql = f"select realname,account from zt_user where realname in ({users_string[:-1]})"
# cursor = connection.cursor()
# cursor.execute(sql)
# lines = cursor.fetchall()
#
# user_map = {}
# for line in lines:
#     user_map[line[0]] = line[1]
#
# # 假设get_jinghao是一个已经定义好的函数
# def user_map_func(x):
#
#     if bool(re.compile(r'^[a-zA-Z0-9]*$').match(x)):
#         return x
#     else:
#         return user_map[x]
#
# for column in ['由谁创建', '指派给', '由谁关闭']:
#     df[column] = df[column].map(user_map_func)
#
# ls = []
#
# for row in df.itertuples():
#     row = list(row)
#
#     # print(row[-9])
#     # print(row[-16])
#     # print(row[-19])
#     # print('######')
#
#     row[-9] = row[-9] if row[-9] != '' else None
#     row[-16] = row[-16] if row[-16] != '' else None
#     row[-19] = row[-19] if row[-19] != '' else None
#
#     ls.append([row[2],row[3],row[4],row[5],row[8],row[10],row[11],row[12],row[15],row[16],row[17],row[19],row[20],row[21],
#               row[22],row[23],row[24],row[29],row[30],row[31],row[32],row[33],row[39],row[40]])
#
# current_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
#
# for i in ls:
#     # SQL 插入语句
#     sql = "INSERT INTO `zt_bug` " \
#           "(`project`,`product`,`injection`,`identify`,`branch`,`module`,`execution`,`plan`,`story`,`storyVersion`,`task`," \
#           "`toTask`,`toStory`,`title`,`keywords`,`severity`,`pri`,`type`,`os`,`browser`,`flawsClass`,`testStage`,`bugEnv`,`bugClass`,`orderNumber`," \
#           "`flawsCause`,`crossDomainCollaboration`,`impactScope`,`ownedMicroservice`,`hardware`,`found`,`steps`,`status`," \
#           "`subStatus`,`color`,`confirmed`,`activatedCount`,`activatedDate`,`openedBy`,`openedDate`,`openedBuild`," \
#           "`assignedTo`,`assignedDate`,`deadline`,`resolvedBy`," \
#           "`resolution`,`resolvedBuild`,`resolvedDate`,`closedBy`,`closedDate`,`duplicateBug`,`case`,`caseVersion`,`feedback`," \
#           "`result`,`repo`,`mr`,`testtask`,`lastEditedBy`,`lastEditedDate`,`deleted`)" \
#           " VALUES " \
#           "(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s," \
#           "%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
#     # 要插入的数据
#     values = (i[2],i[0],0,0,0,i[1],i[3],0,0,1,0,0,0,i[4],'',i[5],i[6],i[7],'','',i[11],i[12],i[13],i[14],'','',i[8],i[9],
#               i[10],'','',i[15],i[16],'','',1,0,None,i[17],i[18],i[19],i[20],i[21],None,'','','',None,'',None,0,0,0,
#               0,0,0,0,0,'',None,'0')
#     # 执行 SQL 语句
#     cursor.execute(sql, values)
#
#     # 提交事务
#     connection.commit()
#
#     lastid = cursor.lastrowid
#
#     # SQL 插入语句
#     sql = "INSERT INTO `zt_action` " \
#           "(`objectType`, `objectID`, `product`, `project`, `execution`, `actor`, `action`,`date`," \
#           "`comment`,`extra`,`read`,`vision`,`efforted`)" \
#           " VALUES " \
#           "(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
#     # 要插入的数据
#     values = (
#         'bug-import', lastid, f',{0},', 0, 0, 'wangcongjie', 'import',
#         current_time,
#         '', '', '0', 'rnd', 0)
#     # 执行 SQL 语句
#     cursor.execute(sql, values)
#
#     # 提交事务
#     connection.commit()


# import pymysql
#
# PYMYSQL_CONF = {
#     'host': '127.0.0.1',
#     # 'host': '172.16.159.132',
#     'password': '123456',
#     # 'password': 'Sqdmm123#',
#     'port': 3306,  # MySQL服务端口
#     'user': 'root',
#     'database': 'zentao',
#     'charset': 'utf8mb4'
# }
#
# # pymysql连接数据
# connection = pymysql.connect(**PYMYSQL_CONF)
# sql = '''
# SELECT
#     t.id id,
#     t.mode mode,
#     t.finishedBy finishedBy,
#     tt.account account,
#     tt.status status
# FROM
#     zt_task t
# LEFT JOIN
#     zt_taskteam tt ON t.id = tt.task
# where deleted='0'
# '''
# with connection.cursor() as cursor:
#     cursor.execute(sql)
#     data = cursor.fetchall()
#
# #任务完成人
# finishedBy = {}
# tasks = []
#
# for i in data:
#
#     tasks.append(i[0])
#
#     if i[1] != '':
#         # #多人任务
#         # if i[-1] == 'done':
#         #     if i[0] not in finishedBy.keys():
#         #         finishedBy[i[0]] = []
#         #     finishedBy[i[0]].append(i[-2])
#         pass
#     else:
#         if i[2] != '':
#             if i[0] not in finishedBy.keys():
#                 finishedBy[i[0]] = []
#             finishedBy[i[0]].append(i[2])
#
# sql = '''
# select id,status , finishedBy, finishedDate from zt_task
# where mode=''
# and deleted='0'
# and status in ('closed','done')
# '''
# with connection.cursor() as cursor:
#     cursor.execute(sql)
#     data = cursor.fetchall()
#
# s = set()
#
# for i in data:
#
#     s.add(i[0])
#
# print(len(finishedBy.keys()),len(s))
#
# print(set(finishedBy.keys())-s)
# print(s-set(finishedBy.keys()))

# sql = '''
# SELECT
#     objectID,actor
# FROM
#     zt_action
# where objectType='task' and action='finished'
# '''
# with connection.cursor() as cursor:
#     cursor.execute(sql)
#     data = cursor.fetchall()
#
# action_finishedBy = {}
#
# for i in data:
#
#     #不在task中的删除
#     if i[0] not in tasks:
#         continue
#
#     if i[0] not in action_finishedBy.keys():
#         action_finishedBy[i[0]] = []
#     action_finishedBy[i[0]].append(i[1])
#
# print(len(finishedBy.keys()),len(action_finishedBy.keys()))
#
# print(set(finishedBy.keys())-set(action_finishedBy.keys()))
# print(set(action_finishedBy.keys())-set(finishedBy.keys()))

import pandas as pd

file = '/media/977GB/virtualbox/开发任务导入模板-20250120150458.xlsx'
df = pd.read_excel(file)
columns = list(df.columns)
df = pd.read_excel(file, dtype={columns[10]: 'str',
                                            columns[11]: 'str',
                                            columns[12]: 'str',
                                            columns[13]: 'str',
                                            columns[14]: 'str'})
df = df.dropna(axis=0, how='all')  # 删掉全为空值的行
df = df.fillna('').astype(str)  #字符串化
print(df['实际开始日期\n（例：\n2024-09-09 05:02:22）'])

